const connection = require('../../sql');

class DataRoleService {
	async listDataRoles({ roleName, page, limit }) {
		const offset = (page - 1) * limit;
		const whereClause = roleName ? `WHERE role_name LIKE ? AND del_flag = 'F'` : `WHERE del_flag = 'F'`;
		const params = roleName ? [`%${roleName}%`] : [];

		const countSql = `SELECT COUNT(*) as total FROM sys_data_role ${whereClause}`;
		const listSql = `
      SELECT id, role_name as roleName, data_scope_cd as dataScopeCd, remark, is_lock as isLock, 
             create_time as createTime, update_time as updateTime
      FROM sys_data_role
      ${whereClause}
      ORDER BY create_time ASC
      LIMIT ? OFFSET ?
    `;
		const listParams = [...params, String(limit), String(offset)];

		const [countResult] = await connection.execute(countSql, params);
		const [rows] = await connection.execute(listSql, listParams);

		for (const row of rows) {
			row.selectMenuIds = await this.getMenuIds(row.id);
			row.selectDeptIds = await this.getDeptIds(row.id);
			row.userOptions = await this.getUserOptions(row.id);
		}

		return {
			current: page,
			limit,
			totalPage: Math.ceil(countResult[0].total / limit),
			total: countResult[0].total,
			rows,
		};
	}

	async getMenuIds(roleId) {
		const sql = `
      SELECT menu_id
      FROM sys_data_role_menu
      WHERE role_id = ?
    `;
		const [result] = await connection.execute(sql, [roleId]);
		return result.map((row) => row.menu_id);
	}

	async getDeptIds(roleId) {
		const sql = `
      SELECT relation_id
      FROM sys_data_role_relation
      WHERE role_id = ? AND relation_type_cd = 'dept'
    `;
		const [result] = await connection.execute(sql, [roleId]);
		return result.map((row) => row.relation_id);
	}

	async getUserOptions(roleId) {
		const sql = `
      SELECT u.id, u.username, u.nickname
      FROM sys_data_role_relation dr
      JOIN sys_user u ON dr.relation_id = u.id
      WHERE dr.role_id = ? AND dr.relation_type_cd = 'user' AND u.del_flag = 'F'
    `;
		const [result] = await connection.execute(sql, [roleId]);
		return result;
	}

	async getMenuAndDeptLists() {
		const menuSql = `
          SELECT id, pid, deep, sort, title, menu_type_cd as menuTypeCd, permissions, name
          FROM sys_menu
          WHERE del_flag = 'F' 
            AND menu_type_cd = '1002002' 
            AND use_data_scope = 'T'
          ORDER BY sort ASC
        `;
		const deptSql = `
          SELECT id, pid, deep, sort, name
          FROM sys_dept
          WHERE del_flag = 'F'
          ORDER BY sort ASC
        `;
		const userSql = `
          SELECT id, username, nickname
          FROM sys_user
          WHERE del_flag = 'F'
        `;

		const [menuRows] = await connection.execute(menuSql);
		const [deptRows] = await connection.execute(deptSql);
		const [userRows] = await connection.execute(userSql);

		// 直接返回平铺菜单，只包含 menu_type_cd = '1002002' 且 use_data_scope = 'T'
		const menuLists = menuRows.map((row) => ({
			id: row.id,
			pid: row.pid,
			deep: row.deep,
			sort: row.sort,
			title: row.title,
			menuTypeCd: row.menuTypeCd,
			permissions: row.permissions,
			name: row.name,
		}));

		// 部门保持树形结构
		const deptLists = this.buildTree(deptRows, 0);

		return {
			menuLists,
			deptLists,
			userOptions: userRows,
		};
	}

	buildTree(rows, parentId) {
		const nodes = [];
		for (const row of rows) {
			if (String(row.pid) === String(parentId)) {
				const children = this.buildTree(rows, row.id);
				nodes.push({
					...row,
					children: children.length ? children : '',
				});
			}
		}
		return nodes;
	}

	async getDataRoleById(id) {
		const sql = `
          SELECT id, role_name as roleName, CAST(data_scope_cd AS UNSIGNED) as dataScopeCd, remark, is_lock as isLock, 
                 create_time as createTime, update_time as updateTime
          FROM sys_data_role
          WHERE id = ? AND del_flag = 'F'
        `;
		const [rows] = await connection.execute(sql, [id]);
		if (rows.length === 0) return null;

		const role = rows[0];
		role.selectMenuIds = await this.getMenuIds(id);
		role.selectDeptIds = await this.getDeptIds(id);

		// 修改 userOptions 只返回 ID 数组
		const userIds = await this.getUserOptions(id);
		role.userOptions = userIds.map((user) => user.id);

		return role;
	}

	async saveDataRole(params, userId) {
		const { roleName, dataScopeCd, selectMenuIds = [], selectDeptIds = [], userOptions = [] } = params;
		const conn = await connection.getConnection();

		try {
			await conn.beginTransaction();

			// 插入 sys_data_role
			const insertRoleSql = `
            INSERT INTO sys_data_role (role_name, data_scope_cd, remark, is_lock, create_id, create_time, del_flag)
            VALUES (?, ?, '', 'F', ?, NOW(), 'F')
          `;
			const [insertResult] = await conn.execute(insertRoleSql, [roleName, dataScopeCd, userId]);
			const roleId = insertResult.insertId;

			// 插入菜单关联（如果有）
			if (selectMenuIds.length > 0) {
				const menuValues = selectMenuIds.map((menuId) => [roleId, menuId]);
				const menuSql = `INSERT INTO sys_data_role_menu (role_id, menu_id) VALUES ?`;
				await conn.query(menuSql, [menuValues]);
			}

			// 插入部门和用户关联（如果有）
			const relationValues = [];
			if (selectDeptIds.length > 0) {
				selectDeptIds.forEach((deptId) => relationValues.push([roleId, 'dept', deptId]));
			}
			if (userOptions.length > 0) {
				userOptions.forEach((userId) => relationValues.push([roleId, 'user', userId]));
			}
			if (relationValues.length > 0) {
				const relationSql = `INSERT INTO sys_data_role_relation (role_id, relation_type_cd, relation_id) VALUES ?`;
				await conn.query(relationSql, [relationValues]);
			}

			await conn.commit();
			return roleId;
		} catch (error) {
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
		}
	}
	async deleteDataRoles(ids) {
		const placeholders = ids.map(() => '?').join(',');
		const conn = await connection.getConnection();

		try {
			await conn.beginTransaction();

			// 删除 sys_data_role_menu
			const deleteMenuSql = `DELETE FROM sys_data_role_menu WHERE role_id IN (${placeholders})`;
			await conn.execute(deleteMenuSql, ids);

			// 删除 sys_data_role_relation
			const deleteRelationSql = `DELETE FROM sys_data_role_relation WHERE role_id IN (${placeholders})`;
			await conn.execute(deleteRelationSql, ids);

			// 删除 sys_data_role
			const deleteRoleSql = `
        UPDATE sys_data_role
        SET del_flag = 'T'
        WHERE id IN (${placeholders}) AND del_flag = 'F'
      `;
			const [result] = await conn.execute(deleteRoleSql, ids);

			await conn.commit();
			return result;
		} catch (error) {
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
		}
	}
	async updateDataRole(params, userId) {
		const { id, roleName, dataScopeCd, remark = '', isLock, selectMenuIds = [], selectDeptIds = [], userOptions = [] } = params;
		const conn = await connection.getConnection();

		try {
			await conn.beginTransaction();

			// 更新 sys_data_role
			const updateRoleSql = `
            UPDATE sys_data_role
            SET role_name = ?, data_scope_cd = ?, remark = ?, is_lock = ?, update_id = ?, update_time = NOW()
            WHERE id = ? AND del_flag = 'F'
          `;
			const [updateResult] = await conn.execute(updateRoleSql, [roleName, dataScopeCd, remark, isLock, userId, id]);
			if (updateResult.affectedRows === 0) {
				await conn.rollback();
				return null;
			}

			// 删除原有菜单关联
			const deleteMenuSql = `DELETE FROM sys_data_role_menu WHERE role_id = ?`;
			await conn.execute(deleteMenuSql, [id]);

			// 插入新的菜单关联（如果有）
			if (selectMenuIds.length > 0) {
				const menuValues = selectMenuIds.map((menuId) => [id, menuId]);
				const menuSql = `INSERT INTO sys_data_role_menu (role_id, menu_id) VALUES ?`;
				await conn.query(menuSql, [menuValues]);
			}

			// 删除原有部门和用户关联
			const deleteRelationSql = `DELETE FROM sys_data_role_relation WHERE role_id = ?`;
			await conn.execute(deleteRelationSql, [id]);

			// 插入新的部门和用户关联（如果有）
			const relationValues = [];
			if (selectDeptIds.length > 0) {
				selectDeptIds.forEach((deptId) => relationValues.push([id, 'dept', deptId]));
			}
			if (userOptions.length > 0) {
				userOptions.forEach((userId) => relationValues.push([id, 'user', userId]));
			}
			if (relationValues.length > 0) {
				const relationSql = `INSERT INTO sys_data_role_relation (role_id, relation_type_cd, relation_id) VALUES ?`;
				await conn.query(relationSql, [relationValues]);
			}

			await conn.commit();
			return id;
		} catch (error) {
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
		}
	}
}

module.exports = new DataRoleService();
